

Select 
	iea.sFolderNumber,
	case when (iea.dtProjectStart = a.ProjectStartDate) then ' ' 
	else IsNull(cast(iea.dtProjectStart as varchar), 'NullValue') + ' | ' + IsNull(cast(a.ProjectStartDate as varchar), 'NullValue') end as StartDate
	,case when (iea.dtProjectEnd = a.ProjectEndDate) then ' ' 
	else IsNull(cast(iea.dtProjectEnd as varchar), 'NullValue') + ' | ' + IsNull(cast(a.ProjectEndDate as varchar), 'NullValue') end as ProjectEndDate
	,case when (iea.dtReceived = a.ReceivedDate) then ' ' 
	else IsNull(cast(iea.dtReceived as varchar), 'NullValue') + ' | ' + IsNull(cast(a.ReceivedDate as varchar), 'NullValue') end as DateReceived
	,case when (iea.sLegacyIdentifier  = a.LegacyIdentifier) then ' ' 
	else IsNull(cast(iea.sLegacyIdentifier as varchar), 'NullValue') + ' | ' + IsNull(cast(a.LegacyIdentifier as varchar), 'NullValue') end as LegacyIdentifier
	,case when (iea.sOverallStatus  = a.OverallStatus) then ' ' 
	else IsNull(cast(iea.sOverallStatus as varchar), 'NullValue') + ' | ' + IsNull(cast(a.OverallStatus as varchar), 'NullValue') end as OverallStatus
	,case when (iea.nPIID  = U.UserName) then ' ' 
	else IsNull(cast(iea.nPIID as varchar), 'NullValue') + ' | ' + IsNull(cast(U.UserName as varchar), 'NullValue') end as PIUserName
	,case when (iea.sAgreementType  = AT.Name) then ' ' 
	else IsNull(cast(iea.sAgreementType as varchar), 'NullValue') + ' | ' + IsNull(cast(AT.Name as varchar), 'NullValue') end as AgreementType
	--,case when (ies.sName  = ASp.Name) then ' ' 
	--else IsNull(cast(ies.sName as varchar), 'NullValue') + ' | ' + IsNull(cast(ASp.Name as varchar), 'NullValue') end as SponsorName
	,case when (iea.sTransactionType  = Trans.Name) then ' ' 
	else IsNull(cast(iea.sTransactionType as varchar), 'NullValue') + ' | ' + IsNull(cast(Trans.Name as varchar), 'NullValue') end as TransactionType
	,case when (iea.sParentFolderNumber  = A.ParentFolderNumber) then ' ' 
	else IsNull(cast(iea.sParentFolderNumber as varchar), 'NullValue') + ' | ' + IsNull(cast(A.ParentFolderNumber as varchar), 'NullValue') end as ParentFolderNumber
	,case when (iea.sAgreementTitle  = cast(A.Title as varchar(8000))) then ' ' 
	else IsNull(cast(iea.sAgreementTitle as varchar), 'NullValue') + ' | ' + IsNull(cast(A.Title as varchar), 'NullValue') end as Title
	--,case when (ief.sPSFundNumber  = AFund.FundNumber) then ' ' 
	--else IsNull(cast(ief.sPSFundNumber as varchar), 'NullValue') + ' | ' + IsNull(cast(AFund.FundNumber as varchar), 'NullValue') end as FundNumber
		

	FROM [MIRAGE].InfoEdStaging.[dbo].tblAgreement iea
	inner join AgrAgreement a 
		on a.PropNumber = iea.prop_No
	left join AdmPerson P
		on P.Id = a.PIPersonId
	left join FwkDomainUser U
		on U.Id = P.FwkDOmainUserId
	left join AgrAGreementType AT
		on AT.Id = A.AgrAgreementTypeId
	left join AgrTransactionType Trans
		on Trans.Id = A.AgrTransactionTypeId
	--left join AgrFund_AgrAgreement_Map FundMap
	--	on A.Id = FundMap.AgrAgreementId
	--left join AgrFund AFund
	--	on AFund.Id = FundMap.AgrFundId
	--left join [MIRAGE].InfoEdStaging.[dbo].tblAgreeFund ief
	--	on ief.prop_No = iea.prop_No

	--left join [MIRAGE].InfoEdStaging.[dbo].tblSponsor ies
	--	on iea.spon_code = ies.spon_code
	--left join SvcEntity_AdmSponsor_Map SponMap
	--	on SponMap.SvcEntityId = A.SvcEntityId
	--left join AdmSponsor ASp
	--	on SponMap.AdmSponsorId = ASp.Id
	WHERE	
--			ISNULL(iea.dtProjectStart, '') = ISNULL(a.ProjectStartDate, '') AND
--			ISNULL(iea.dtProjectEnd, '') = ISNULL(a.ProjectEndDate, '') AND
--			ISNULL(iea.dtReceived, '') = ISNULL(a.ReceivedDate, '') AND
--			ISNULL(iea.sLegacyIdentifier, '') = ISNULL(a.LegacyIdentifier, '') AND
--			ISNULL(iea.sOverallStatus, '') = ISNULL(a.OverallStatus, '') AND
--			ISNULL(iea.nPIID, '') = ISNULL(U.UserName, '') 		 AND
--			ISNULL(iea.sAgreementType, '') = ISNULL(AT.Name, '') AND
--			--ISNULL(ies.sName, '') <> ISNULL(ASp.Name, '') --OR
--			ISNULL(iea.sTransactionType , '' ) = ISNULL(Trans.Name, '') AND
--			ISNULL(iea.sParentFolderNumber, '') = ISNULL(A.ParentFolderNumber, '') AND
--			ISNULL(iea.sAgreementTitle, '') = ISNULL(cast(A.Title as varchar(8000)), '') 
--			

			ISNULL(iea.dtProjectStart, '') <> ISNULL(a.ProjectStartDate, '') OR
			ISNULL(iea.dtProjectEnd, '') <> ISNULL(a.ProjectEndDate, '') OR
			ISNULL(iea.dtReceived, '') <> ISNULL(a.ReceivedDate, '') OR
			ISNULL(iea.sLegacyIdentifier, '') <> ISNULL(a.LegacyIdentifier, '') OR
			ISNULL(iea.sOverallStatus, '') <> ISNULL(a.OverallStatus, '') OR
			ISNULL(iea.nPIID, '') <> ISNULL(U.UserName, '') 		 OR
			ISNULL(iea.sAgreementType, '') <> ISNULL(AT.Name, '') OR
			--ISNULL(ies.sName, '') <> ISNULL(ASp.Name, '') --OR
			ISNULL(iea.sTransactionType , '' ) <> ISNULL(Trans.Name, '') OR
			ISNULL(iea.sParentFolderNumber, '') <> ISNULL(A.ParentFolderNumber, '') OR
			ISNULL(iea.sAgreementTitle, '') <> ISNULL(cast(A.Title as varchar(8000)), '') 
			--ISNULL(ief.sPSFundNumber, '') <> ISNULL(AFund.FundNumber, '') 
	

	
	--Select prop_No, count(1) as FundCount into tmpInfoEdFundCount from [MIRAGE].InfoEdStaging.[dbo].tblAgreeFund ief group by prop_no
	
	--Select A.PropNumber, count(1) as FundCount into tmpInsightFundCount from AgrAgreement A	inner join 	AgrFund_AgrAgreement_Map FundMap 
	--		on A.Id = FundMap.AgrAgreementId group by A.PropNumber
	

--	select I.Prop_No, AG.folderNumber, I.FundCount as InfoEdCount, A.FundCount as InsightCount
--		from tmpInfoEdFundCount I
--		inner join tmpInsightFundCount A
--			on I.Prop_No = A.PropNumber
--		inner join AgrAgreement AG
--			on AG.PropNumber = A.PropNumber
--		where I.FundCount <> A.FundCount

--select top 10 * from AGrAgreement
--select top 10 * from AGrFund
--select top 10 * from [MIRAGE].InfoEdStaging.[dbo].tblAgreement
--select top 10 * from [MIRAGE].InfoEdStaging.[dbo].tblSponsor
--select top 10 * from [MIRAGE].InfoEdStaging.[dbo].tblAgreeFund
--select count(1) from [MIRAGE].InfoEdStaging.[dbo].tblAgreeFund
--sp_addlinkedserver 'MIRAGE'

